package Dao;

import Entity.Merchandise;
import Entity.Merchant;

import java.sql.*;
import java.util.*;

public class McdDao {
    private static Connection connection = null;

    private static void openDB() throws ClassNotFoundException, SQLException {
        // JDBC访问MySQL数据库
        // 1. 将连接MySQL的Jar包引入到工程中
        // 2. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3. 获取数据库连接
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ecommerce?characterEncoding=utf-8&serverTimezone=GMT%2B8",
                "root",
                "lty63481960");
    }

    private static void closeDB() throws SQLException {
        connection.close();
    }

    public static List<Merchandise> queryByKey(String key,String username) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句

        String presql = null;
        if(username==null||username.isEmpty()) presql = "select * from merchant";
        else presql = "select * from merchant where username='" + username +"'";
        String sql = "select * from merchandise order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(presql);
        Set<Integer> idset = new HashSet<>();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            idset.add(id);
        }

        // 7. 处理结果集
        resultSet = statement.executeQuery(sql);
        ArrayList<Merchandise> list = new ArrayList<>();
        while(resultSet.next()){
            if(idset.contains(resultSet.getInt("mctid"))){
                String id = resultSet.getString("id");
                String name = resultSet.getString("mcdname");
                double price = resultSet.getDouble("price");
                int num = resultSet.getInt("num");
                int mctid = resultSet.getInt("mctid");
                String url = resultSet.getString("url");
                Merchandise merchandise = new Merchandise(id, name, price, num, mctid, url);

                list.add(merchandise);
            }
        }

        if(key!=null||!key.isEmpty()) Collections.sort(list, new Comparator<Merchandise>() {
                @Override
                public int compare(Merchandise o1, Merchandise o2) {
                    if(o1.getId().equals(key)) return -1;
                    if(o2.getId().equals(key)) return 1;
                    if(String.valueOf(o1.getMctid()).equals(key)) return -1;
                    if(String.valueOf(o2.getMctid()).equals(key)) return 1;
                    if(o1.getName().contains(key)&&o2.getName().contains(key)&&o1.getName().length()!=o2.getName().length())
                        return o1.getName().length() - o2.getName().length();
                    int n1=0,n2=0;
                    if(o1.getId().contains(key)) n1++;
                    if(o1.getName().contains(key)) n1++;
                    if(String.valueOf(o1.getPrice()).contains(key)) n1++;
                    if(String.valueOf(o1.getNum()).contains(key)) n1++;
                    if(String.valueOf(o1.getMctid()).contains(key)) n1++;
                    if(o2.getId().contains(key)) n2++;
                    if(o2.getName().contains(key)) n2++;
                    if(String.valueOf(o2.getPrice()).contains(key)) n2++;
                    if(String.valueOf(o2.getNum()).contains(key)) n2++;
                    if(String.valueOf(o2.getMctid()).contains(key)) n2++;

                    if(n2==n1) return o2.getId().compareTo(o1.getId());
                    return n2 - n1;
                }
            });
        // 8. 关闭资源
        resultSet.close();
        statement.close();
        closeDB();

        return list;
    }

    public static Merchandise queryByid(String id) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "select * from merchandise where id='" + id + "' order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(sql);
        // 7. 处理结果集
        Merchandise merchandise = null;
        if(resultSet.next()){
            String name = resultSet.getString("mcdname");
            double price = resultSet.getDouble("price");
            int num = resultSet.getInt("num");
            int mctid = resultSet.getInt("mctid");
            String url = resultSet.getString("url");
            merchandise = new Merchandise(id, name, price, num, mctid, url);
//            System.out.println(merchandise);
        }
        // 8. 关闭资源
        resultSet.close();
        statement.close();
        closeDB();

        return merchandise;
    }

    public static ArrayList<Merchandise> queryBymctid(int mctid) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "select * from merchandise where mctid=" + mctid +" order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(sql);
        // 7. 处理结果集
        ArrayList<Merchandise> list = new ArrayList<>();
        while(resultSet.next()){
            String id = resultSet.getString("id");
            String name = resultSet.getString("mcdname");
            double price = resultSet.getDouble("price");
            int num = resultSet.getInt("num");
            String url = resultSet.getString("url");
            Merchandise merchandise = new Merchandise(id, name, price, num, mctid, url);

            list.add(merchandise);
        }
        // 8. 关闭资源
        resultSet.close();
        statement.close();
        closeDB();

        return list;
    }

    public static void update(Merchandise merchandise) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "update merchandise set mcdname=?, price=?, num=?, mctid=?, url=? where id=?";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数

        preparedStatement.setString(1, merchandise.getName());
        preparedStatement.setDouble(2, merchandise.getPrice());
        preparedStatement.setInt(3, merchandise.getNum());
        preparedStatement.setInt(4, merchandise.getMctid());
        preparedStatement.setString(5,merchandise.getUrl());
//        System.out.println(merchandise);
        preparedStatement.setString(6, merchandise.getId());
        // 7. 执行修改
        preparedStatement.execute();

        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }

    public static String queryusername(int mctid) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "select username from merchant where id=" + mctid +" order by id desc";
        // 5. 创建Statement
        Statement statement = connection.createStatement();
        // 6. 执行Statement
        ResultSet resultSet = statement.executeQuery(sql);
        // 7. 处理结果集
        while(resultSet.next()){
            return resultSet.getString("username");
        }
        return "admin";
    }

    public static void save(Merchandise merchandise) throws SQLException, ClassNotFoundException {
//        System.out.println(merchandise);
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "insert into merchandise (id, mcdname, price, num, mctid, url) value (?, ?, ?, ?, ?, ?)";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数
        preparedStatement.setString(1, merchandise.getId());
        preparedStatement.setString(2, merchandise.getName());
        preparedStatement.setDouble(3, merchandise.getPrice());
        preparedStatement.setInt(4, merchandise.getNum());
        preparedStatement.setInt(5, merchandise.getMctid());
        preparedStatement.setString(6,merchandise.getUrl());

        // 7. 执行插入
        preparedStatement.execute();

        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }

    public static void del(String id) throws SQLException, ClassNotFoundException {
        openDB();
        // 4. 编写查询city表的sql语句
        String sql = "delete from merchandise where id = ?";
        // 5. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6. 设置参数
        preparedStatement.setString(1, id);
        // 7. 执行删除
        preparedStatement.execute();
        // 8. 关闭资源
        preparedStatement.close();
        closeDB();
    }

}
